package orest.front.achieve;

import java.sql.Connection;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class AchieveSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static AchieveSql m_builder = new AchieveSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static AchieveSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    
    /**
     * 科研单位推荐状态修改
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String changeUnitStatus(String unitUuids,String fieldname,String status)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("UPDATE T_UNIT SET ").append(fieldname);
        sb.append(" = '").append(status).append("'");
        String expertUuidArr[] = unitUuids.split(",");
    	sb.append(" WHERE UNITID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    /**
     * 根据ID获得成果信息
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getKjcgById(String kjcgid)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("SELECT T.* FROM T_KJCG T WHERE 1=1 ");
        if(!kjcgid.equals("")){
        	sb.append(" AND T.KJCGID='").append(kjcgid).append("'");
        }
        
        return sb.toString();
    }
    
    
    /**
     * 科技成果列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getKjcgList(RequestKit requestKit)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("SELECT T.KjcgID,T.kjcg_item_3 AS KJCGNAME FROM T_KJCG T WHERE 1=1 ");
        String newEnergyClassType = requestKit.getParameter("newEnergyClassType", "");
        if(!newEnergyClassType.equals("")){
        	if(newEnergyClassType.indexOf(".")==-1){
	        	sb.append(" AND NewEnergyClass1 = '").append(newEnergyClassType).append("' ");
        	}else{
        		String strArr[] = newEnergyClassType.split("\\.");
        		String class1 = strArr[0];

        		String class2 = strArr[1];
        		sb.append(" AND NewEnergyClass1 = '").append(class1).append("' ");
        		sb.append(" AND NewEnergyClass2 = '").append(class2).append("' ");
        	}
        }
        
        
        sb.append(" ORDER BY KJCG_ITEM_121 DESC");
        return sb.toString();
    }
    
    
    
    /**
     * 获取子分类列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getSubFlByFatherOptValue(String tablename,String fieldname,String fatheroptvalue)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME FROM T_FIELDOPTION T ");
        sb.append(" WHERE T.TABLENAME='").append(tablename).append("'");
        sb.append(" AND T.FIELDNAME='").append(fieldname).append("'");
        sb.append(" AND T.OPTIONVALUE LIKE '").append(fatheroptvalue).append(".%'");
        sb.append(" ORDER BY T.TABLENAME,T.FIELDNAME,T.OPTIONVALUE,T.OPTIONNAME");
        return sb.toString();
    }
    
    
    
    
    /**
     * 获得推荐科技成果列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getRecommendKjcgList(RequestKit requestKit)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("SELECT T.KJCGID,T.KJCG_ITEM_3 KJCGNAME,S.UNITID,S.ITEM_9 UNITNAME ");
        sb.append(" FROM T_KJCG T LEFT JOIN T_UNIT S ON T.UNITID = S.UNITID WHERE 1=1 ");
        
        //当showType为HomepageShow时推荐到首页，为ChannelShow时推荐到频道
        String showType = requestKit.getParameter("showType", "");
        if(showType.equals("HomepageShow")){
        	sb.append(" AND T.HOMEPAGESHOW = '1'");
        }
        if(showType.equals("ChannelShow")){
        	sb.append(" AND T.CHANNELSHOW = '1'");
        }
        sb.append(" ORDER BY KJCG_ITEM_121 DESC");
        return sb.toString();
    }
    
    
    /**
     * 获得新能源分类列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getNewEnergyClassList()
    {
    	StringBuffer sb = new StringBuffer();
        sb.append(" SELECT T.OPTIONVALUE,T.OPTIONNAME FROM T_FIELDOPTION T ");
        sb.append(" WHERE T.TABLENAME='common'");
        sb.append(" AND T.FIELDNAME='NewEnergyClass'");
        sb.append(" ORDER BY T.OPTIONVALUE");
        return sb.toString();
    }
    
}
